package com.solidtrust.dao;

import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.solidtrust.entity.CarCategory;
import com.solidtrust.entity.IScrapeEntity;

@Service
public class CommonDao {

	private static final Logger log4j = Logger.getLogger(CommonDao.class);
	
	@PersistenceContext EntityManager em;
	
	public Long lastId(Class<? extends IScrapeEntity> clazz){
		String ql = "select max(id) from " + clazz.getSimpleName();
		Long result = em.createQuery(ql, Long.class).getSingleResult();
		return result == null ? 0L : result;
	}
	
	public IScrapeEntity findBySourceId(String sourceId, Class<? extends IScrapeEntity> clazz){
		String ql = "from " + clazz.getSimpleName() + " where sourceId = '" + sourceId + "'";
		List<? extends IScrapeEntity> results = em.createQuery(ql, clazz).getResultList();
		return results.isEmpty() ? null : results.get(0);
	}
	
	public boolean hasSold(String sourceId, Class<? extends IScrapeEntity> clazz){
		String ql = "select count(c) from " + clazz.getSimpleName() + " c where sourceId = '" + sourceId + "' and removedDate is not null";
		return (em.createQuery(ql, Long.class).getSingleResult() > 0);
	}
	public boolean unSold(String sourceId, Class<? extends IScrapeEntity> clazz){
		String ql = "select count(c) from " + clazz.getSimpleName() + " c where sourceId = '" + sourceId + "' and removedDate is null";
		return (em.createQuery(ql, Long.class).getSingleResult() > 0);
	}
	
	public Map<String, Long> getUnsoldCarIds(Class<? extends IScrapeEntity> clazz){
		String ql = "select sourceId, id from "+clazz.getSimpleName()+" where removedDate is null";
		List<Object[]> list = em.createQuery(ql, Object[].class).getResultList();
		Map<String, Long> map = new HashMap<>();
		for (Object[] objects : list) {
			map.put((String)objects[0], (Long)objects[1]);
		}
		return map;
	}
	
	public Map<String, Long> getUnsoldCarIds(Class<? extends IScrapeEntity> clazz, CarCategory cc){
		String ql = "select sourceId, id from "+clazz.getSimpleName()+" where removedDate is null and city = '" + cc.getCity() + "' and brand = '" + cc.getBrand() + "'";
		List<Object[]> list = em.createQuery(ql, Object[].class).getResultList();
		Map<String, Long> map = new HashMap<>();
		for (Object[] objects : list) {
			map.put((String)objects[0], (Long)objects[1]);
		}
		return map;
	}
	
	public Long countUnsoldCarIds(Class<? extends IScrapeEntity> clazz){
		String ql = "select count(c) from "+clazz.getSimpleName()+" c where removedDate is null";
		return em.createQuery(ql, Long.class).getSingleResult();
	}
	
	public Map<String, Long> getSoldCarIds(Class<? extends IScrapeEntity> clazz){
		String ql = "select sourceId, id from "+clazz.getSimpleName()+" where removedDate is not null";
		List<Object[]> list = em.createQuery(ql, Object[].class).getResultList();
		Map<String, Long> map = new HashMap<>();
		for (Object[] objects : list) {
			map.put((String)objects[0], (Long)objects[1]);
		}
		return map;
	}
	
	public Map<String, Long> getSoldCarIds(Class<? extends IScrapeEntity> clazz, CarCategory cc){
		String ql = "select sourceId, id from "+clazz.getSimpleName()+" where removedDate is not null and city = '" + cc.getCity() + "' and brand = '" + cc.getBrand() + "'";
		List<Object[]> list = em.createQuery(ql, Object[].class).getResultList();
		Map<String, Long> map = new HashMap<>();
		for (Object[] objects : list) {
			map.put((String)objects[0], (Long)objects[1]);
		}
		return map;
	}
	
	public Long countSoldCarIds(Class<? extends IScrapeEntity> clazz){
		String ql = "select count(c) from "+clazz.getSimpleName()+" c where removedDate is not null";
		return em.createQuery(ql, Long.class).getSingleResult();
	}
	
	@Transactional(readOnly = false, propagation = Propagation.REQUIRES_NEW)
	public void saveBatch(List<?> list){
		for (Object object : list) {
			em.merge(object);
		}
	}
	
	@Transactional(readOnly = false, propagation = Propagation.REQUIRES_NEW)
	public void save(Object object){
		em.merge(object);
	}
	
	public List<String> getBrands(String source){
		String ql = "select brand from Brand where source = '" + source + "'";
		return em.createQuery(ql, String.class).getResultList();
	}
	
	public List<String> getModels(String source){
		String ql = "select model from Model where source = '" + source + "'";
		return em.createQuery(ql, String.class).getResultList();
	}
	
	@Transactional(readOnly = false)
	public void touch(Collection<Long> ids, Date touchDate, Class<? extends IScrapeEntity> clzz){
		if(ids.size() > 0){
			String ql = "update " + clzz.getSimpleName() + " set touchDate = :touchDate where id in (:ids)";
			int count = em.createQuery(ql)
				.setParameter("touchDate", touchDate)
				.setParameter("ids", ids)
				.executeUpdate();
			log4j.info("touched " + count + " records");
		}
	}
	@Transactional(readOnly = false)
	public void touchBySourceId(Collection<String> carIds, Date touchDate, Class<? extends IScrapeEntity> clzz){
		if(carIds.size() > 0){
			String ql = "update " + clzz.getSimpleName() + " set touchDate = :touchDate where sourceId in (:ids)";
			int count = em.createQuery(ql)
					.setParameter("touchDate", touchDate)
					.setParameter("ids", carIds)
					.executeUpdate();
			log4j.info("touched " + count + " records");
		}
	}
	
	@Transactional(readOnly = false)
	public void markDelete(List<Long> ids, Date removedDate, Class<? extends IScrapeEntity> clzz){
		if(ids.size() > 0){
			String ql = "update " + clzz.getSimpleName() + " set removedDate = :removedDate where id in (:ids)";
			int count = em.createQuery(ql)
				.setParameter("removedDate", removedDate)
				.setParameter("ids", ids)
				.executeUpdate();
			log4j.info("deleted " + count + " records");
		}
	}
	
	@Transactional(readOnly = false)
	public void markDelete(Date currentDate, Class<? extends IScrapeEntity> clzz){
		String ql = "update " + clzz.getSimpleName() + " set removedDate = :currentDate where touchDate < :currentDate";
		int count = em.createQuery(ql)
				.setParameter("currentDate", currentDate)
				.executeUpdate();
		log4j.info("deleted " + count + " records");
	}
	
	@Transactional(readOnly = false)
	public void markUnsold(Class<? extends IScrapeEntity> clazz, long id, Date touchDate){
		IScrapeEntity en = em.find(clazz, id);
		en.setRemovedDate(null);
		en.setTouchDate(touchDate);
		em.persist(en);
	}
	
	@Transactional(readOnly = false)
	public void setTransactionPrice(Class<? extends IScrapeEntity> clazz, int price, long id){
		IScrapeEntity en = em.find(clazz, id);
		en.setTransactionPrice(price);
		em.persist(en);
	}
	
	public Map<Long, String> getSoldWithoutTrasactionPrice(Class<? extends IScrapeEntity> clazz){
		String ql = "select id, adUrl from " + clazz.getSimpleName() + " where removedDate is not null and transactionPrice = 0";
		List<Object[]> list = em.createQuery(ql, Object[].class).getResultList();
		Map<Long, String> map = new HashMap<>();
		for (Object[] objects : list) {
			map.put((Long)objects[0], (String)objects[1]);
		}
		return map;
	}
	
	public CarCategory findOneByUnlock(String source){
		String ql = "from CarCategory where locking = false and source = '" + source + "'";
		List<CarCategory> list = em.createQuery(ql, CarCategory.class).setMaxResults(1).getResultList();
		return list.isEmpty() ? null : list.get(0);
	}
	
	@Transactional(readOnly = false)
	public int unlockCategories(String source){
		String ql = "update CarCategory set locking = false where source = '" + source + "'";
		return em.createQuery(ql).executeUpdate();
	}
}
